﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDACompanyDeliveryDistrict
    {
        public static List<CompanyDeliveryDistrict> Select(CompanyDeliveryDistrict companyDeliveryDistrict)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            string NotEffective = ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString();
            con.Open();
            string _cmdText =
                            "SELECT c.* " +
                            "FROM companydeliverydistrict c " +
                            "WHERE ((c.ID = " + companyDeliveryDistrict.ID.ToString() + ") OR (" + companyDeliveryDistrict.ID.ToString() + " = " + NotEffective + "))" +
                            "AND ((c.CompanyID=" + companyDeliveryDistrict.Company.ID.ToString() + ")OR(" + companyDeliveryDistrict.Company.ID.ToString() + "=" + NotEffective + "))" +
                            "AND ((c.PostalCode='" + companyDeliveryDistrict.PostalCode + "')OR('" + companyDeliveryDistrict.PostalCode + "'='" + NotEffective + "'))" +
                            "ORDER BY c.ID";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<CompanyDeliveryDistrict> companyDeliveryDistrictList = new List<CompanyDeliveryDistrict>();

            CompanyDeliveryDistrict _companyDeliveryDistrict;
            foreach (DataRow row in tbl.Rows)
            {
                _companyDeliveryDistrict = new CompanyDeliveryDistrict();
                _companyDeliveryDistrict.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));
                
                _companyDeliveryDistrict.Company = new Company();
                _companyDeliveryDistrict.Company.ID = (row["CompanyID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CompanyID"]));
                
                _companyDeliveryDistrict.PostalCode = (row["PostalCode"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue).ToString() : row["PostalCode"].ToString());
                _companyDeliveryDistrict.MinOrderPrice = (row["MinOrderPrice"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["MinOrderPrice"].ToString()));

                companyDeliveryDistrictList.Add(_companyDeliveryDistrict);
            }
            con.Close();
            return companyDeliveryDistrictList;

        }
    }
}